package com.bo.Dao.Impl;


import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.support.AbstractLobCreatingPreparedStatementCallback;
import org.springframework.jdbc.core.support.SqlLobValue;
import org.springframework.jdbc.support.lob.LobCreator;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import com.bo.Dao.CompanyTouchDao;
import com.bo.Entity.CompanyTouch;
import com.bo.Entity.MyImage;
import com.bo.util.MyLobStreamingResultSetExtractor;


@Repository
public class CompanyTouchDaoImpl implements CompanyTouchDao{

	@Autowired
	private JdbcTemplate jdbcTemplate;
	
	@Autowired
	private LobHandler lobHandler;
	
	
	

	public List<CompanyTouch> getCompanyTouch(int id) {

		String sql="select * from company where id=?";
		
		RowMapper<CompanyTouch> mapper = new RowMapper<CompanyTouch>() {
			public CompanyTouch mapRow(ResultSet rs, int rowNum) throws SQLException {

				CompanyTouch ct=new CompanyTouch();
				
				ct.setId(rs.getInt("id"));
				ct.setName(rs.getString("name"));
				ct.setWebSite(rs.getString("web_site"));
				ct.setAddress(rs.getString("address"));
				ct.setEmail(rs.getString("email"));
				ct.setPhone(rs.getString("phone"));
				ct.setFax(rs.getString("fax"));
				ct.setLatitude(rs.getDouble("latitude"));
				ct.setLongitude(rs.getDouble("longitude"));
				
				ct.setImage(new MyImage(1,rs.getString("image_name")));

                return ct;
			}
		};
		
	    return jdbcTemplate.query(sql, new Object[]{id}, mapper);
	     
	}

	

	

	public int update ( final CompanyTouch ct ) throws DataAccessException{
		
		String sql="UPDATE company SET web_site=?,phone=?,fax=?," +
				"email=?,address=?,latitude=?,longitude=?,image_name=?,image=? WHERE id= ?";
		
		
		MyImage image=ct.getImage();
		
		SqlLobValue lobValue=new SqlLobValue(image.getInputStream(), (int) image.getSize(), lobHandler);
		
		return 
		jdbcTemplate.update(sql ,new Object[] {ct.getWebSite()
		  ,ct.getPhone(),ct.getFax(),ct.getEmail(),ct.getAddress(),ct.getLatitude(),ct.getLongitude()
		  ,image.getImageName(),lobValue,ct.getId()}
		  ,new int[]{Types.VARCHAR,Types.VARCHAR,Types.VARCHAR,Types.VARCHAR
				    ,Types.VARCHAR,Types.DOUBLE,Types.DOUBLE,Types.VARCHAR,Types.BLOB,Types.INTEGER});
		
	}
	
	
	

	public void getImage(final int id,final MyImage image){
		
		String sql="SELECT image FROM company WHERE id=?";
		
		jdbcTemplate.query(sql, new Object[] {id}
		           ,new MyLobStreamingResultSetExtractor(lobHandler, id, image));
	}


}
